
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 08/18/2013 00:20:29
-- Generated from EDMX file: D:\Soft Winform\searchandbooking\Soft Booking\PhanMemDatPhongKhachSan\Models\ModelDB.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [BookingDBForWinForm];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_TblBooking_TblAgent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TblBooking] DROP CONSTRAINT [FK_TblBooking_TblAgent];
GO
IF OBJECT_ID(N'[dbo].[FK_TblBookingUseFacility_TblBooking]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TblBookingUseFacility] DROP CONSTRAINT [FK_TblBookingUseFacility_TblBooking];
GO
IF OBJECT_ID(N'[dbo].[FK_TblBookingUseFacility_TblFacility]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TblBookingUseFacility] DROP CONSTRAINT [FK_TblBookingUseFacility_TblFacility];
GO
IF OBJECT_ID(N'[dbo].[FK_TblPrice_TblRoomType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TblPrice] DROP CONSTRAINT [FK_TblPrice_TblRoomType];
GO
IF OBJECT_ID(N'[dbo].[FK_TblRoom_TblRoomType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TblRoom] DROP CONSTRAINT [FK_TblRoom_TblRoomType];
GO
IF OBJECT_ID(N'[dbo].[FK_TblRoomBooked_TblBooking]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TblRoomBooked] DROP CONSTRAINT [FK_TblRoomBooked_TblBooking];
GO
IF OBJECT_ID(N'[dbo].[FK_TblRoomBooked_TblRoom]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TblRoomBooked] DROP CONSTRAINT [FK_TblRoomBooked_TblRoom];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[sysdiagrams]', 'U') IS NOT NULL
    DROP TABLE [dbo].[sysdiagrams];
GO
IF OBJECT_ID(N'[dbo].[TblAgent]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TblAgent];
GO
IF OBJECT_ID(N'[dbo].[TblBooking]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TblBooking];
GO
IF OBJECT_ID(N'[dbo].[TblBookingUseFacility]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TblBookingUseFacility];
GO
IF OBJECT_ID(N'[dbo].[TblFacility]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TblFacility];
GO
IF OBJECT_ID(N'[dbo].[TblPrice]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TblPrice];
GO
IF OBJECT_ID(N'[dbo].[TblRoom]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TblRoom];
GO
IF OBJECT_ID(N'[dbo].[TblRoomBooked]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TblRoomBooked];
GO
IF OBJECT_ID(N'[dbo].[TblRoomType]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TblRoomType];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'sysdiagrams'
CREATE TABLE [dbo].[sysdiagrams] (
    [name] nvarchar(128)  NOT NULL,
    [principal_id] int  NOT NULL,
    [diagram_id] int IDENTITY(1,1) NOT NULL,
    [version] int  NULL,
    [definition] varbinary(max)  NULL
);
GO

-- Creating table 'TblAgents'
CREATE TABLE [dbo].[TblAgents] (
    [AgentID] int IDENTITY(1,1) NOT NULL,
    [Fullname] nvarchar(max)  NULL,
    [Address] nvarchar(max)  NULL,
    [Phone] nvarchar(50)  NULL,
    [Email] nvarchar(max)  NULL,
    [Genres] bit  NULL,
    [IdentificationNO] nvarchar(max)  NULL,
    [Country] nvarchar(50)  NULL,
    [Notes] nvarchar(max)  NULL
);
GO

-- Creating table 'TblBookings'
CREATE TABLE [dbo].[TblBookings] (
    [BookingID] int IDENTITY(1,1) NOT NULL,
    [AgentID] int  NOT NULL,
    [CheckIn] datetime  NULL,
    [CheckOut] datetime  NULL,
    [TotalGuest] int  NULL,
    [TotalRoom] int  NULL,
    [IsComfirm] bit  NULL
);
GO

-- Creating table 'TblBookingUseFacilities'
CREATE TABLE [dbo].[TblBookingUseFacilities] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [BookingID] int  NOT NULL,
    [FacilityID] int  NOT NULL,
    [Quantum] smallint  NOT NULL,
    [DateUse] datetime  NOT NULL
);
GO

-- Creating table 'TblFacilities'
CREATE TABLE [dbo].[TblFacilities] (
    [FacilityID] int IDENTITY(1,1) NOT NULL,
    [FacilityName] nvarchar(max)  NULL,
    [Price] decimal(18,0)  NOT NULL
);
GO

-- Creating table 'TblPrices'
CREATE TABLE [dbo].[TblPrices] (
    [PriceID] int  NOT NULL,
    [RoomTypeID] int  NULL,
    [Price] decimal(18,0)  NULL,
    [ApplyDate] datetime  NULL,
    [EndDate] datetime  NULL
);
GO

-- Creating table 'TblRooms'
CREATE TABLE [dbo].[TblRooms] (
    [RoomID] int IDENTITY(1,1) NOT NULL,
    [TypeID] int  NOT NULL,
    [Status] smallint  NULL
);
GO

-- Creating table 'TblRoomBookeds'
CREATE TABLE [dbo].[TblRoomBookeds] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [BookingID] int  NOT NULL,
    [RoomID] int  NOT NULL
);
GO

-- Creating table 'TblRoomTypes'
CREATE TABLE [dbo].[TblRoomTypes] (
    [RoomTypeID] int IDENTITY(1,1) NOT NULL,
    [TypeName] nvarchar(max)  NULL,
    [Description] nvarchar(max)  NULL,
    [TotalRoom] int  NULL,
    [TotalPeople] int  NULL,
    [DefaultPrice] decimal(18,0)  NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [diagram_id] in table 'sysdiagrams'
ALTER TABLE [dbo].[sysdiagrams]
ADD CONSTRAINT [PK_sysdiagrams]
    PRIMARY KEY CLUSTERED ([diagram_id] ASC);
GO

-- Creating primary key on [AgentID] in table 'TblAgents'
ALTER TABLE [dbo].[TblAgents]
ADD CONSTRAINT [PK_TblAgents]
    PRIMARY KEY CLUSTERED ([AgentID] ASC);
GO

-- Creating primary key on [BookingID] in table 'TblBookings'
ALTER TABLE [dbo].[TblBookings]
ADD CONSTRAINT [PK_TblBookings]
    PRIMARY KEY CLUSTERED ([BookingID] ASC);
GO

-- Creating primary key on [ID] in table 'TblBookingUseFacilities'
ALTER TABLE [dbo].[TblBookingUseFacilities]
ADD CONSTRAINT [PK_TblBookingUseFacilities]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [FacilityID] in table 'TblFacilities'
ALTER TABLE [dbo].[TblFacilities]
ADD CONSTRAINT [PK_TblFacilities]
    PRIMARY KEY CLUSTERED ([FacilityID] ASC);
GO

-- Creating primary key on [PriceID] in table 'TblPrices'
ALTER TABLE [dbo].[TblPrices]
ADD CONSTRAINT [PK_TblPrices]
    PRIMARY KEY CLUSTERED ([PriceID] ASC);
GO

-- Creating primary key on [RoomID] in table 'TblRooms'
ALTER TABLE [dbo].[TblRooms]
ADD CONSTRAINT [PK_TblRooms]
    PRIMARY KEY CLUSTERED ([RoomID] ASC);
GO

-- Creating primary key on [ID] in table 'TblRoomBookeds'
ALTER TABLE [dbo].[TblRoomBookeds]
ADD CONSTRAINT [PK_TblRoomBookeds]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [RoomTypeID] in table 'TblRoomTypes'
ALTER TABLE [dbo].[TblRoomTypes]
ADD CONSTRAINT [PK_TblRoomTypes]
    PRIMARY KEY CLUSTERED ([RoomTypeID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [AgentID] in table 'TblBookings'
ALTER TABLE [dbo].[TblBookings]
ADD CONSTRAINT [FK_TblBooking_TblAgent]
    FOREIGN KEY ([AgentID])
    REFERENCES [dbo].[TblAgents]
        ([AgentID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TblBooking_TblAgent'
CREATE INDEX [IX_FK_TblBooking_TblAgent]
ON [dbo].[TblBookings]
    ([AgentID]);
GO

-- Creating foreign key on [BookingID] in table 'TblBookingUseFacilities'
ALTER TABLE [dbo].[TblBookingUseFacilities]
ADD CONSTRAINT [FK_TblBookingUseFacility_TblBooking]
    FOREIGN KEY ([BookingID])
    REFERENCES [dbo].[TblBookings]
        ([BookingID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TblBookingUseFacility_TblBooking'
CREATE INDEX [IX_FK_TblBookingUseFacility_TblBooking]
ON [dbo].[TblBookingUseFacilities]
    ([BookingID]);
GO

-- Creating foreign key on [BookingID] in table 'TblRoomBookeds'
ALTER TABLE [dbo].[TblRoomBookeds]
ADD CONSTRAINT [FK_TblRoomBooked_TblBooking]
    FOREIGN KEY ([BookingID])
    REFERENCES [dbo].[TblBookings]
        ([BookingID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TblRoomBooked_TblBooking'
CREATE INDEX [IX_FK_TblRoomBooked_TblBooking]
ON [dbo].[TblRoomBookeds]
    ([BookingID]);
GO

-- Creating foreign key on [FacilityID] in table 'TblBookingUseFacilities'
ALTER TABLE [dbo].[TblBookingUseFacilities]
ADD CONSTRAINT [FK_TblBookingUseFacility_TblFacility]
    FOREIGN KEY ([FacilityID])
    REFERENCES [dbo].[TblFacilities]
        ([FacilityID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TblBookingUseFacility_TblFacility'
CREATE INDEX [IX_FK_TblBookingUseFacility_TblFacility]
ON [dbo].[TblBookingUseFacilities]
    ([FacilityID]);
GO

-- Creating foreign key on [RoomTypeID] in table 'TblPrices'
ALTER TABLE [dbo].[TblPrices]
ADD CONSTRAINT [FK_TblPrice_TblRoomType]
    FOREIGN KEY ([RoomTypeID])
    REFERENCES [dbo].[TblRoomTypes]
        ([RoomTypeID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TblPrice_TblRoomType'
CREATE INDEX [IX_FK_TblPrice_TblRoomType]
ON [dbo].[TblPrices]
    ([RoomTypeID]);
GO

-- Creating foreign key on [TypeID] in table 'TblRooms'
ALTER TABLE [dbo].[TblRooms]
ADD CONSTRAINT [FK_TblRoom_TblRoomType]
    FOREIGN KEY ([TypeID])
    REFERENCES [dbo].[TblRoomTypes]
        ([RoomTypeID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TblRoom_TblRoomType'
CREATE INDEX [IX_FK_TblRoom_TblRoomType]
ON [dbo].[TblRooms]
    ([TypeID]);
GO

-- Creating foreign key on [RoomID] in table 'TblRoomBookeds'
ALTER TABLE [dbo].[TblRoomBookeds]
ADD CONSTRAINT [FK_TblRoomBooked_TblRoom]
    FOREIGN KEY ([RoomID])
    REFERENCES [dbo].[TblRooms]
        ([RoomID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TblRoomBooked_TblRoom'
CREATE INDEX [IX_FK_TblRoomBooked_TblRoom]
ON [dbo].[TblRoomBookeds]
    ([RoomID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------